#! /usr/local/bin/python
"""
This file is part of Clipf2db.

Clipf2db is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

Clipf2db is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with Clipf2db. If not, see <http://www.gnu.org/licenses/>.
					
"""
import ConfigParser, psycopg2 as dbapi2
from datetime import datetime

class DatabaseAccess():
    """ Connecting to the database. """ 

    def get_myconf(self):
        """ myconf """
        return self._myconf

    def set_myconf(self, release):
        """ set myconf """
        self._myconf = release

    myconf = property(get_myconf, set_myconf)
 
    def get_dbhost(self):
        """ dbhost """
        return self._dbhost

    def set_dbhost(self, version):
        """ set dbhost """
        self._dbhost = version

    dbhost = property(get_dbhost, set_dbhost)

    def get_dbname(self):
        """ dbname """
        return self._dbname

    def set_dbname(self, version):
        """ set dbname """
        self._dbname = version

    dbname = property(get_dbname, set_dbname)

    def get_dbuser(self):
        """ dbuser """
        return self._dbuser

    def set_dbuser(self, version):
        """ set dbuser """
        self._dbuser = version

    dbuser = property(get_dbuser, set_dbuser)

    def get_dbpass(self):
        """ dbpass """
        return self._dbpass
    
    def set_dbpass(self, version):
        """ set dbpass """
        self._dbpass = version

    dbpass = property(get_dbpass, set_dbpass)

    def __init__(self):
        """ Initialise the database class. """ 
        self.set_myconf(self.ConfFile())
        self.Config()
        self.tblfinance = 'T_FINANCE'
        self.tblstocks = 'T_STOCK'
        self.tblstockname = 'T_STOCK_NAME'
        self.tblcurstocks = 'T_STOCK_CURRENT'
        self.tblbets = 'T_BET'
        self.tblbetresults = 'T_BET_RESULT'
        self.tblcurbets = 'T_BET_CURRENT'
        self.tables = [self.tblfinance, self.tblstocks, self.tblcurstocks, self.tblbets, self.tblbetresults, self.tblcurbets]

        self.sqlpath = 'sql/'
        self.sqldrop = '01_drop_tables.sql'
        self.sqlcreate = ['01_create_T_FINANCE.sql', '02_create_T_STOCKS.sql', '03_create_T_CURSTOCKS.sql', '04_create_T_BETS.sql', '05_create_T_BETRESULTS.sql', '06_create_T_CURBETS.sql']
        self.msgHandler = __import__('messagehandler')

    def ConfFile(self):
        """ Get config file path from XDG_CONFIG_DIR. """
        # TODO: get from env var
        #return '~/.config/clipf2db/clipf2db.rc'
        return 'config/clipf2db.rc'

    def Config(self):
        """ Retrieve config file values """
        config = ConfigParser.RawConfigParser()
        config.read(self.get_myconf())
        
        dbhost = config.get('database', 'host')[1:-1]
        dbname = config.get('database', 'name')[1:-1]
        dbuser = config.get('database', 'user')[1:-1]
        dbpass = config.get('database', 'password')[1:-1]
        self.set_dbhost(dbhost)        
        self.set_dbname(dbname)        
        self.set_dbuser(dbuser)        
        self.set_dbpass(dbpass)        

    def Setup(self):
        """ Setup the db. """
        msgObj = self.msgHandler.MessageHandler()
        print "Setting up the database..."  
        self.SetupTables(); 
        msgObj.PrintAction('Created table', self.tables)
        print "Fill in known values..."
        self.FillTables()
        msgObj.PrintAction('Added known values to table', self.tables)
        msgObj = None
    
    def SetupTables(self):
        """ The actual creation of the tables. """
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            try:
                for script in self.sqlcreate:
                    sqlfile = "%s/%s" % (str(self.sqlpath),str(script))
                    procedures = open(sqlfile,'r').read()
                    cur.execute(procedures)
                    db.commit()
            finally:
                cur.close()
                db.close()
        except dbapi2.DatabaseError, e:
            print "Error: procedures: %s" % str(e)
            exit(1)

    def FillTables(self): 
        """ Fill in values we already know. """
        #now = datetime.now()
        #db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
        #cur = db.cursor()
        #db.commit()
        #cur.close()
        #db.close()
 
    def Remove(self):
        """ Remove the tables + data from the db. """
        msgObj = self.msgHandler.MessageHandler()
        answer = msgObj.Confirmation('remove all tables from the database')
        if answer == 0:
            self.RemoveTables()
            msgObj = self.msgHandler.MessageHandler()
            msgObj.PrintAction('Removed table', self.tables)
        msgObj = None
       
    def GetValues(self, qry):
        """ Global wrapper for retrieving values. """
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            cur.execute (qry)
            rows = cur.fetchall()
            values = []
            for row in rows:
                i = 0
                for col in row:
                    values.append(row[i])
                    i = i+1
            db.commit()
            cur.close()
            db.close()
            return values
        except dbapi2.DatabaseError, e:
            print "Error: procedures: %s" % str(e)
            exit(1)
 
    def RemoveTables(self):
        """ The actual removal of the tables. """
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            try:
                sqlfile = "%s/%s" % (str(self.sqlpath),str(self.sqldrop))
                procedures = open(sqlfile,'r').read()
                cur.execute(procedures)
            finally:
                db.commit()
                cur.close()
                db.close()
        except dbapi2.DatabaseError, e:
            print "Error: procedures: %s" % str(e)
            exit(1)

    def CopyFile2DB(self, fields_db):
        """ Convert general financial information. """
        #TODO: put this in the inherited class
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            try:
                    now = datetime.now()
                    date_create = now.strftime("%Y-%m-%d %H:%M:%S")
                    date_modify = now.strftime("%Y-%m-%d %H:%M:%S")

                    cur = db.cursor()
                    cur.execute("insert into " + self.tblfinance + "(date, account, product, amount, flag, comment, date_create, date_modify) values('" + fields_db['date'] + "','" + fields_db['acc'] + "','" + fields_db['prod'] + "','" + fields_db['amount'] + "','" + fields_db['flag'] + "','" + fields_db['comment'] + "','" + date_create + "','" + date_modify + "');")
            finally:
                db.commit()
                cur.close()
                db.close()
            self.Progress()
        except dbapi2.DatabaseError, e:
            print "Error: procedures: %s" % str(e)
            exit(1)

    def ConvertStocks(self, fields_comment_db):
        """" Convert general stock information. """
        #TODO: put this in the inherited dbaccess
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            try:
                    now = datetime.now()
                    date_create = now.strftime("%Y-%m-%d %H:%M:%S")
                    date_modify = now.strftime("%Y-%m-%d %H:%M:%S")
                    
                    cur = db.cursor()
                    cur.execute("select  max(id) from " + self.tblfinance + ";")
                    id = cur.fetchone()[0]
                    #TODO: insert in T_STOCK_NAME too
                    #TODO: check if already exists in T_STOCK_NAME (if not exists?)
                    #insert into t_stock_name(name, mid) select 'test2',2 where not exists(select name from t_stock_name where name = 'test2' and mid= 2);

                    cur.execute("insert into " + self.tblstocks + \
                            "(id, snid, mcode, action, price, qty, date_create, date_modify) values(" + \
                            str(id) + ", select snid from " + self.tblstockname + " where name ='" + fields_comment_db['name'] + \
                            "', '" + fields_comment_db['mcode'] + "', '" + fields_comment_db['action'] + "', " + \
                            fields_comment_db['price'] + ", " + fields_comment_db['qty'] + ", '" + date_create + "', '" + date_modify + "');")
            finally:
                    db.commit()
                    cur.close()
                    db.close()
            self.Progress()
        except dbapi2.DatabaseError, e:
            print "Error: procedures: %s" % str(e)
            exit(1)
    
    def ConvertStocksCurrent(self, fields_comment_db):
        """ Upgrade table with current stock information. """
        #TODO: put this in the inherited dbaccess
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            try:
                    now = datetime.now()
                    date_create = now.strftime("%Y-%m-%d %H:%M:%S")
                    date_modify = now.strftime("%Y-%m-%d %H:%M:%S")
                    
                    cur = db.cursor()
                    # important: only insert date_create, don't update it! use only date_modify for updates
                    cur.execute("select  max(id) from " + self.tblfinance + ";")
                    try:
                        id = cur.fetchone()[1]
                        #search  for last added  stock in tblcurstocks
                        print 'id=' + id,
                        cur.execute("select id from " + self.tblcurstocks + " where id = " + id + ";")
                        try:    
                            #update values
                            #date_modify = new value
                            #qty = old + new -> we need to get the old value first
                            cur.execute("select qty from " + self.tblcurstocks + " where id = " + id + ";")
                            oldqty = cur.fetchone()
                            newqty = oldqty + fields_comment_db['qty']
                            if newqty <> 0:
                                cur.execute("update " + self.tblcurstocks + " set qty = " + newqty + ";")
                                # amount = old + new, but only do this when we still have qty left
                            else:
                                #sum qty = 0: delete entry from tblcurstocks
                                cur.execute("delete from " + self.tblcurstocks + " where id=" + id + ";")
                        except:    
                            print 'Empty recordset = new entry'
                            cur.execute("insert into " + self.tblcurstocks + "(id, name, mcode, price, qty, date_create, date_modify) values(" + str(id) + ", '" + fields_comment_db['name'] + "', '" + fields_comment_db['mcode'] + "', " + fields_comment_db['price'] + ", " + fields_comment_db['qty'] + ", '" + date_create + "', '" + date_modify + "');")
                    except:
                        #TODO: find out why it doesn't fetch an id and comes straight to this part
                        #print 'Error in Current stock',
                        print '\b\b+>',
            finally:
                db.commit()
                cur.close()
                db.close()
            self.Progress()
        except dbapi2.DatabaseError, e:
            print "Error: procedures: %s" % str(e)
            exit(1)

    def ConvertBets(self, fields_comment_db):
        """ Convert general betting information. """
        #TODO: put this in the inherited dbaccess
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            try:
                    now = datetime.now()
                    date_create = now.strftime("%Y-%m-%d %H:%M:%S")
                    date_modify = now.strftime("%Y-%m-%d %H:%M:%S")
                    
                    cur = db.cursor() 
                    sqlstr = "select  max(id) from " + self.tblfinance + ";"
                    cur.execute(sqlstr)
                    try:
                        id = cur.fetchone()[0]
                        if fields_comment_db['scoreA'] != '-1' or fields_comment_db['scoreB'] != '-1':
                            # results of the bet
                            sqlstr = "insert into " + self.tblbetresults + "(id, teamA, teamB, choice, gain, scoreA, scoreB, date_create, date_modify)  values('" + str(id) + "','" + fields_comment_db['teamA'] + "', '" + fields_comment_db['teamB'] + "', '" + fields_comment_db['choice'] + "', " + fields_comment_db['pool'] + ", " + fields_comment_db['scoreA']  + "," + fields_comment_db['scoreB'] + ", '" + date_create + "', '" + date_modify + "');"
                        else:
                            sqlstr = "insert into " + self.tblbets + "(id, teamA, teamB, choice, pool, date_create, date_modify)  values('" + str(id) + "','" + fields_comment_db['teamA'] + "', '" + fields_comment_db['teamB'] + "', '" + fields_comment_db['choice'] + "', " + fields_comment_db['pool'] + ", '" + date_create + "', '" + date_modify + "');"
                        #print 'test: sqlstr=' + sqlstr
                        cur.execute(sqlstr)
                    except Exception, err:
                        print str(err)
            finally:
                db.commit()
                cur.close()
                db.close()
            self.Progress()
        except dbapi2.DatabaseError, e:
            print "Error: procedures: %s" % str(e)
            exit(1)

    def ConvertBetsCurrent(self, fields_comment_db):
        """ Upgrade table with current betting information. """
        #TODO: put this in the inherited dbaccess
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            try:
                    cur = db.cursor()
                    sqlstr = "select  max(id) from " + self.tblfinance + "));"
                    cur.execute(sqlstr)

                    try:            
                        id = cur.fetchone()[0]
                        try:
                            #sum qty = 0: delete entry from tblcurstocks
                            sqlstr = "delete from " + self.tblcurbets + " where teamA='" + fields_comment_db['teamA'] + "' and teamB='" + fields_comment_db['teamB'] +"';"
                            cur.execute(sqlstr)
                        except:
                            #print 'Empty recordset = new entry'
                            sqlstr = "insert into " + self.tblcurbets + "(teamA, teamB, choice, pool, scoreA, scoreB, date_create, date_modify)  values('" + fields_comment_db['teamA'] + "', '" + fields_comment_db['teamB'] + "', '" + fields_comment_db['choice'] + "', " + fields_comment_db['pool'] + ", " + fields_comment_db['scoreA']  + "," + fields_comment_db['scoreB'] + ", '" + date_create + "', '" + date_modify + "');"
                            cur.execute(sqlstr)
                        #else:
                        #    #update values
                        #    #date_modify = new value
                        #    #qty = old + new -> we need to get the old value first
                        #    cur.execute("select  from " + self.tblcurbets + " where id = " + id + ";")
                        #    oldqty = cur.fetchone()
                        #    newqty = oldqty + fields_comment_db['qty']
                        #    if newqty <> 0:
                        #        cur.execute("update " + self.tblcurbets + " set qty = " + newqty + ";")
                        #        # amount = old + new, but only do this when we still have qty left
                    except:
                        print 'Empty recordset'
            finally:
                db.commit()
                cur.close()
                db.close()
            self.Progress()
        except dbapi2.DatabaseError, e:
            print "Error: procedures: %s" % str(e)
            exit(1)

    def CheckEmpty(self):
        """ Check if the tables are empty. """
        #TODO: put this in DataBaseAccess and make more generic (return 0 or -1)
        retVal = 0 
        try:
            db = dbapi2.connect(host=self.get_dbhost(),database=self.get_dbname(), user=self.get_dbuser(), password=self.get_dbpass())
            cur = db.cursor()
            #TODO: also check other tables? 
            cur.execute("select id, date, account, product, amount, flag, comment, tags from " + self.tblfinance + " where id = (select max(id) from " + self.tblfinance + ");")
            if cur.fetchone() is not None:
                msgObj = self.msgHandler.MessageHandler()
                msgObj.PrintAction('Table not empty:', ['finance']);
                msgObj = None
                retVal = 1
        finally: 
            cur.close()
            db.close()
            return retVal

    def Progress(self):
        """ Show progress information. """
        #TODO: Put this in a generic way in MessageHandler
        # TODO: select max(lines) from textfile - comment lines
        # that is 100%, with each convert: self.set_progress(self.get_progress()+1)
        # and show the %? => with print '\b\b\b' (tested, it works)
        print '\b\b|>',

